package com.sinosoft.report;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sinosoft.util.CheckEmpty;

@Service("reportManager")
public class ReportManagerImpl implements ReportManager {

	private ReportUserDao reportuser;

	@Autowired
	public void setReportUserDao(ReportUserDao reportuser) {
		this.reportuser = reportuser;
	}

	public List<ReportView> searchUser(ReportView view) {
		StringBuffer sql = new StringBuffer();
		if (!CheckEmpty.isEmpty(view.getStrgroup())) {
			sql.append("select " + view.getStrgroup()
					+ ",count(*) as num from User where 1=1 ");
		} else {
			sql.append("select count(*) as num from User where 1=1 ");
		}

		if (!CheckEmpty.isEmpty(view.getCompany())) {
			sql.append(" and company='" + view.getCompany() + "'");
		}
		if (!CheckEmpty.isEmpty(view.getTerritory())) {
			sql.append(" and territory='" + view.getTerritory() + "'");
		}
		if (!CheckEmpty.isEmpty(view.getGrade())
				&& Integer.parseInt(view.getGrade()) > 0) {
			sql.append(" and grade=" + view.getGrade());
		}
		if (!CheckEmpty.isEmpty(view.getStartime())) {
			sql.append("and REGISTERTIME >= to_date('" + view.getStartime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getEndtime())) {
			sql.append("and REGISTERTIME <= to_date('" + view.getEndtime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getStrgroup())) {
			sql.append(" group by " + view.getStrgroup());
		}
		if (!CheckEmpty.isEmpty(view.getStrorder())) {
			sql.append(" order by " + view.getStrorder() + " "
					+ view.getSortDesc());
		}

		String g = view.getStrgroup();

		String[] group = null;
		if (!CheckEmpty.isEmpty(g)) {
			g = g + ",num";
		} else {
			g = "num";
		}
		group = g.split(",");

		return reportuser.searchUser(sql.toString(), group);
	}

	public List<ReportView> searchUser(String group, String startime,
			String endtime) {
		if (CheckEmpty.isEmpty(group)) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("select " + group + ", count(*) from User where 1=1 ");
		if (!CheckEmpty.isEmpty(startime)) {
			sql.append(" and registertime >= to_date('" + startime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (!CheckEmpty.isEmpty(endtime)) {
			sql.append(" and registertime <= to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		sql.append(" group by " + group);
		return reportuser.searchUser(sql.toString());
	}

	public List<ReportView> searchOrder(ReportView view) {
		StringBuffer hql = new StringBuffer();
		String group = view.getStrgroup();
		if (!CheckEmpty.isEmpty(group)) {
			hql.append("select " + group
					+ ",o.orderstate,count(o.orderid) as num from OrderInfo o ");
			if (group.indexOf("u.company") >= 0) {
				hql.append(" ,User u where o.username = u.username ");
				if (!CheckEmpty.isEmpty(view.getCompany())) {
					hql.append(" and u.company = " + view.getCompany());
				}
			} else {
				if (!CheckEmpty.isEmpty(view.getCompany())) {
					hql.append(" ,User u where o.username = u.username and u.company = '"
							+ view.getCompany()+"'");
				}
			}
		} else {
			hql
					.append("select o.orderstate,count(*) as num from OrderInfo o ");
			if (!CheckEmpty.isEmpty(view.getCompany())) {
				hql.append(",User u where o.username = u.username and u.company = '"
						+ view.getCompany()+"'");
			}
		}
		if(hql.toString().indexOf("where")<0){
			hql.append(" where 1=1 ");
		}
		
		if (!CheckEmpty.isEmpty(view.getSatellte())) {
			hql.append(" and o.satelliteid = '" + view.getSatellte()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getSensor())) {
			hql.append(" and o.sensorid = '" + view.getSensor()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getReceivestation())) {
			hql.append(" and o.receivestationid = '" + view.getReceivestation()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getStartime())) {
			hql.append(" and o.tasktime >= to_date('" + view.getStartime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getEndtime())) {
			hql.append(" and o.tasktime <= to_date('" + view.getEndtime()
					+ "','yyyy-mm-dd')");
		}
		String strgroup = view.getStrgroup();
		if (!CheckEmpty.isEmpty(strgroup)) {
			strgroup = strgroup + ",o.orderstate";
		} else {
			strgroup = "o.orderstate";
		}
		hql.append(" group by " + strgroup);

		if (!CheckEmpty.isEmpty(view.getStrorder())) {
			hql.append(" order by " + view.getStrorder() + " "
					+ view.getSortDesc());
		}

		if (!CheckEmpty.isEmpty(group)) {
			group = group + ",o.orderstate,o.num";
		} else {
			group = "o.orderstate,o.num";
		}
		String[] selectvalue = group.split(",");
		String[] grouplist = new String[selectvalue.length];
		for (int i = 0; i < selectvalue.length; i++) {
			String strvalue = selectvalue[i];
			grouplist[i] = strvalue.substring(strvalue.indexOf(".") + 1);
		}
		List resultlist = reportuser.searchUser(hql.toString(), grouplist);
		return resultlist;
	}

	public List<ReportView> searchOrder(String group, String startime,
			String endtime) {
		if (CheckEmpty.isEmpty(group)) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		if (group.equals("company")) {
			sql
					.append("select u.company, count(*) from OrderInfo o,User u where u.username=o.username ");
		} else {
			sql.append("select " + group
					+ ", count(*) from OrderInfo o where 1=1 ");
		}
		if (!CheckEmpty.isEmpty(startime)) {
			sql.append(" and o.tasktime >= to_date('" + startime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (!CheckEmpty.isEmpty(endtime)) {
			sql.append(" and o.tasktime <= to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (group.equals("company")) {
			sql.append(" group by u.company");
		} else {
			sql.append(" group by o." + group);
		}
		return reportuser.searchUser(sql.toString());
	}

	public List<ReportView> searchCollect(ReportView view) {
		StringBuffer hql = new StringBuffer();
		String group = view.getStrgroup();
		if (!CheckEmpty.isEmpty(group)) {
			hql
					.append("select "
							+ group
							+ ",c.taskStatus,count(*) as num from CollectInfo c ");
			if (group.indexOf("u.company") >= 0) {
				hql.append(" ,User u where c.username = u.username ");
				if (!CheckEmpty.isEmpty(view.getCompany())) {
					hql.append(" and u.company = '" + view.getCompany()+"'");
				}
			} else {
				if (!CheckEmpty.isEmpty(view.getCompany())) {
					hql.append(" ,User u where c.username = u.username and u.company = '"
							+ view.getCompany()+"'");
				}
			}
		} else {
			hql
					.append("select c.taskStatus,count(*) as num from CollectInfo c ");
			if (!CheckEmpty.isEmpty(view.getCompany())) {
				hql.append(" ,User u where c.username = u.username and u.company = '"
						+ view.getCompany()+"'");
			}
		}
		if(hql.toString().indexOf("where")<0){
			hql.append(" where 1=1 ");
		}
		if (!CheckEmpty.isEmpty(view.getSatellte())) {
			hql.append(" and c.satelliteid like '%" + view.getSatellte()+"%'");
		}
		if (!CheckEmpty.isEmpty(view.getSensor())) {
			hql.append(" and c.sensorid like '%" + view.getSensor()+"%'");
		}
		if (!CheckEmpty.isEmpty(view.getStartime())) {
			hql.append(" and c.subdate >= to_date('" + view.getStartime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getEndtime())) {
			hql.append(" and c.subdate <= to_date('" + view.getEndtime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getStartime())) {
			hql.append(" and c.endtime >= to_date('"
					+ view.getCollectstarttime() + "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getEndtime())) {
			hql.append(" and c.endtime <= to_date('" + view.getCollectendtime()
					+ "','yyyy-mm-dd')");
		}
		String strgroup = view.getStrgroup();
		if (!CheckEmpty.isEmpty(strgroup)) {
			strgroup = strgroup + ",c.taskStatus";
		} else {
			strgroup = "c.taskStatus";
		}
		hql.append(" group by " + strgroup);

		if (!CheckEmpty.isEmpty(view.getStrorder())) {
			hql.append(" order by " + view.getStrorder() + " "
					+ view.getSortDesc());
		}

		if (!CheckEmpty.isEmpty(group)) {
			group = group + ",c.taskStatus,c.num";
		} else {
			group = "c.taskStatus,c.num";
		}
		String[] selectvalue = group.split(",");
		String[] grouplist = new String[selectvalue.length];
		for (int i = 0; i < selectvalue.length; i++) {
			String strvalue = selectvalue[i];
			grouplist[i] = strvalue.substring(strvalue.indexOf(".") + 1);
		}
		List resultlist = reportuser.searchUser(hql.toString(), grouplist);
		return resultlist;
	}

	public List<ReportView> searchCollect(String group, String startime,
			String endtime) {
		if (CheckEmpty.isEmpty(group)) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		if (group.equals("company")) {
			sql
					.append("select u.company, count(*) from CollectInfo c,User u where c.username=u.username ");
		} else {
			sql.append("select " + group
					+ ", count(*) from CollectInfo c where 1=1 ");
		}
		if (!CheckEmpty.isEmpty(startime)) {
			sql.append(" and c.subdate >= to_date('" + startime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (!CheckEmpty.isEmpty(endtime)) {
			sql.append(" and c.subdate <= to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (group.equals("company")) {
			sql.append(" group by u.company");
		} else {
			sql.append(" group by c." + group);
		}
		return reportuser.searchUser(sql.toString());
	}

	private void sethql(StringBuffer hql, ReportView view) {
		hql.append(",User u where d.createuser = u.username ");
		if (!CheckEmpty.isEmpty(view.getCompany())) {
			hql.append(" and u.company = '" + view.getCompany()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getTerritory())) {
			hql.append(" and u.territory = '" + view.getTerritory()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getGrade())) {
			hql.append(" and u.grade = '" + view.getGrade()+"'");
		}
	}

	public List<ReportView> searchDownloadSize(ReportView view) {
		StringBuffer hql = new StringBuffer();
		String group = view.getStrgroup();
		if (!CheckEmpty.isEmpty(group)) {
			hql
					.append("select "
							+ group
							+ ",sum(d.filesize) as num from FileObject d ");
			if (group.indexOf("u.territory") >= 0
					|| group.indexOf("u.grade") >= 0) {
				sethql(hql, view);
			} else {
				if (!CheckEmpty.isEmpty(view.getCompany())
						|| !CheckEmpty.isEmpty(view.getTerritory())
						|| !CheckEmpty.isEmpty(view.getGrade())) {
					sethql(hql, view);
				}
			}
		} else {
			hql
					.append("select sum(d.filesize) as num from FileObject d ");
			if (!CheckEmpty.isEmpty(view.getCompany())
					|| !CheckEmpty.isEmpty(view.getTerritory())
					|| !CheckEmpty.isEmpty(view.getGrade())) {
				sethql(hql, view);
			}
		}
		if(hql.toString().indexOf("where")<0){
			hql.append(" where 1=1 ");
		}
		if (!CheckEmpty.isEmpty(view.getUsername())) {
			hql.append(" and d.createuser = '" + view.getUsername()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getFilename())) {
			hql.append(" and d.filename like '%" + view.getFilename() + "%'");
		}
		if (!CheckEmpty.isEmpty(view.getIp())) {
			hql.append(" and d.createip = '" + view.getIp()+"'");
		}
		if (!CheckEmpty.isEmpty(view.getSatellte())) {
			hql.append(" and d.satelliteid like '%" + view.getSatellte()+"%'");
		}
		if (!CheckEmpty.isEmpty(view.getSensor())) {
			hql.append(" and d.sensorid like '" + view.getSensor()+"%'");
		}
		if (!CheckEmpty.isEmpty(view.getStartime())) {
			hql.append(" and d.createtime >= to_date('" + view.getStartime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getEndtime())) {
			hql.append(" and d.createtime <= to_date('" + view.getEndtime()
					+ "','yyyy-mm-dd')");
		}
		if (!CheckEmpty.isEmpty(view.getStrgroup())) {
			hql.append(" group by " + view.getStrgroup());
		}
		if (!CheckEmpty.isEmpty(view.getStrorder())) {
			hql.append(" order by " + view.getStrorder() + " "
					+ view.getSortDesc());
		}

		if (!CheckEmpty.isEmpty(group)) {
			group = group + ",d.num";
		} else {
			group = "d.num";
		}
		String[] selectvalue = group.split(",");
		String[] grouplist = new String[selectvalue.length];
		for (int i = 0; i < selectvalue.length; i++) {
			String strvalue = selectvalue[i];
			grouplist[i] = strvalue.substring(strvalue.indexOf(".") + 1);
		}
		List resultlist = reportuser.searchUser(hql.toString(), grouplist);
		return resultlist;
	}

	public List<ReportView> searchDownloadSize(String group, String startime,
			String endtime) {
		if (CheckEmpty.isEmpty(group)) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		if (group.equals("u.territory") || group.equals("u.grade")) {
			sql
					.append("select "
							+ group
							+ ",sum(d.filesize) from FileObject d,User u where d.createuser=u.username ");
		} else {
			sql.append("select " + group
					+ ",sum(d.filesize) from FileObject d where 1=1 ");
		}
		if (!CheckEmpty.isEmpty(startime)) {
			sql.append(" and d.createtime >= to_date('" + startime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		if (!CheckEmpty.isEmpty(endtime)) {
			sql.append(" and d.createtime <= to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss')");
		}
		sql.append(" group by " + group);
		return reportuser.searchUser(sql.toString());
	}
	
	public List<ReportView> searchVisitNum(String group, String startime, String endtime){
		if (CheckEmpty.isEmpty(group)) {
			return null;
		}
		StringBuffer sql=new StringBuffer();
		sql.append("select to_char(t.logindate,'"+group+"'), count(*) from zywx_user_count t where 1=1 ");
		if (!CheckEmpty.isEmpty(startime)) {
			sql.append(" and to_char(t.logindate,'yyyy-MM-dd') >= '"+startime+"'");
		}
		if (!CheckEmpty.isEmpty(endtime)) {
			sql.append(" and to_char(t.logindate,'yyyy-MM-dd') <= '"+endtime+"'");
		}
		sql.append(" group by to_char(t.logindate,'"+group+"') order by to_char(t.logindate,'"+group+"')");
		return reportuser.searchvisit(sql.toString());
	}
}
